﻿
using BCCommon;
using BCCommon.TMS.Order;
using BCCommon.TMS.Transport;
using BCEntity.TMS.Driver;
using BCEntity.TMS.KanBan.AgentKanBan;
using BCEntity.TMS.Order.Order;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.Order.Order
{
    /// <summary>
    /// 订单
    /// </summary>
    public class OrderData : IOrderData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM orders";
        private const string QUERY = @"SELECT OrderId,Consignee,DriverRemark,MapTruckModeId,MapTruckTypeId,DriverId,DriverName,VehicleNo,OrderStatus,IsInsuredPrice,IsCod,IsReceipt,
                                            ReceiptPhoto,ConsigneePhone,ReceiptAcceptancePhoto,IsInvoice,OrderAmount,FreightCostAmount,TotalDistanceLength,NotifyDriveTime,
                                            DriveOrderPromising,VehicleStartOutTime,CloseOrderTime,CloseOrderContent,ConsigneeAddress,InvoiceAmount,ShippingPayTime,
                                            ConsigneePayTime,IsSettlement,CloseEditerId,CloseEditer,Remark,AssignEditerId,AssignEditer,GoodsPhoto,ShippingGender,ConsigneeGender,
                                            CompanyId,CompanyName,AgentRecordId,OneAgentRecordId,TwoAgentRecordId,FenceId,GoodsName,GoodsWeight,GoodsVolume,GoodsLength,GoodsWidth,
                                            GoodsHeight,OrderNo,PayMode,IsBook,VehicleTransportBusinessConfigId,TransportBusiness,TransportBusinessStr,SuiteBusinessType,
                                            SuiteBusinessName,VehicleSuiteBusinessConfigId,SuiteName,VehicleBusinessMappingId,TransportOrderId,VehicleTypeId,VehicleTypeName,
                                            VehicleCategory,VehicleCategoryName,FreightConfigurationId,FreightConfigurationExtentionId,HorsemanFreightConfigurationId,
                                            HorsemanFreightExtentionId,InsuredConfigId,StartPrice,TransportOrderNo,StartWeight,StartMileage,ContinueMileage,ContinueWeight,
                                            ContinueMileagePrice,ContinueWeightPrice,ContinueMileageAmount,ContinueWeightAmount,SpecificServiceTitleStr,SpecificServiceAmount,
                                            SourceType,MoveHouseSpecificationService,MoveHouseServiceAmount,SuiteForCrowdTitle,SuiteForCrowdRemark,SuiteServiceTitle,
                                            SuiteServiceRemark,CarryServiceAmount,MarketingCouponId,SuiteCouponAmount,CouponAmount,UserId,PackingTitleStr,PackingAmount,
                                            NightFreightOrderExtractProportion,NightFreightAdditionalAmount,InsuredPriceCertificates,InsuredPrice,InsuredPriceCharge,
                                            IsInsuredPriceHigh,InsuredClaimWeight,InsuredClaimCount,Shipping,InsuredClaimPay,CodCost,CodCharge,CodGoodsValueCertificates,
                                            InsuredPriceAmount,InsuredPriceChargeAmount,TimeoutClaimPayAmount,InsuredClaimPayAmount,CodCostAmount,CodChargeAmount,
                                            ShippingPhone,MusangAmount,ProvinceCode,ProvinceName,CityCode,CityName,CountyCode,CountyName,Longitude,Latitude,EndProvinceCode,
                                            ShippingAddress,EndProvinceName,EndCityCode,EndCityName,EndCountyCode,EndCountyName,EndLongitude,EndLatitude,CreateTime,BookTime,
                                            CompleteTime,IssuanceInvoice,BigGoodsNumber,TakeGoodsCode,WithCarPeopleNumber,WithCarPeoplePhone,IsInsuredContact,BookArrivalTime,
                                            AgentBranchesConfigId,OrderAmountSettleResult,OrderSignPhoto,OrderSignTime,OrderSignPerson,OrderSignPersonPhone
                                        FROM orders";
        public OrderData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 添加订单
        public async Task<OrderEntity> Add(OrderEntity orderEntity)
        {
            string[] orderParams = new string[]{"TransportOrderId","TransportOrderNo",
                "OrderNo", "SourceType","IsVip", "UserId", "Shipping", "ShippingPhone", "ShippingAddress","Consignee","ConsigneePhone","ConsigneeAddress","DriverRemark",
                "ProvinceCode","CityCode","CountyCode","ProvinceName","CityName","CountyName","Longitude","Latitude","ShippingGender","ConsigneeGender",
                "EndProvinceCode","EndCityCode","EndCountyCode","EndProvinceName","EndCityName","EndCountyName","EndLongitude","EndLatitude",
                "GoodsPhoto","GoodsName","GoodsWeight","GoodsVolume","GoodsLength","GoodsWidth","GoodsHeight","TakeGoodsCode",
                "InsuredConfigId","InsuredPriceCertificates","InsuredPrice","InsuredPriceCharge","IsInsuredPriceHigh","InsuredPriceAmount","InsuredPriceChargeAmount",
                "IsBook", "BookTime","PayMode","VehicleTransportBusinessConfigId","TransportBusiness","TransportBusinessStr","MarketingCouponId","CouponAmount",
                "NightFreightOrderExtractProportion","NightFreightAdditionalAmount","VehicleSuiteBusinessConfigId","SuiteName","SuiteBusinessType","SuiteBusinessName",
                "VehicleBusinessMappingId","VehicleTypeId","VehicleTypeName","VehicleCategory","VehicleCategoryName","WithCarPeopleNumber","WithCarPeoplePhone",
                "FreightType","FreightConfigurationId","FreightConfigurationExtentionId","HorsemanFreightConfigurationId","HorsemanFreightExtentionId",
                "StartPrice","StartWeight","StartMileage","ContinueMileage","ContinueWeight","ContinueMileagePrice","ContinueWeightPrice","ContinueMileageAmount","ContinueWeightAmount",
                "MoveHouseSpecificationService","MoveHouseServiceAmount","SuiteCouponAmount",
                "SuiteForCrowdTitle","SuiteForCrowdRemark","SuiteServiceTitle","SuiteServiceRemark","CarryServiceAmount",
                "BigGoodsNumber","PackingTitleStr","PackingAmount","SpecificServiceTitleStr","SpecificServiceAmount",
                "CodCost","CodCharge","CodGoodsValueCertificates","CodCostAmount","CodChargeAmount","MusangAmount",
                "MapTruckTypeId","MapTruckModeId",
                "IsInsuredPrice","IsCod","IsReceipt","IsInvoice","IsInsuredContact","BookArrivalTime","OrderAmountSettleResult","AgentBranchesConfigId",
                "OrderAmount","FreightCostAmount","TotalDistanceLength","CompanyId","CompanyName","AgentRecordId","OneAgentRecordId","TwoAgentRecordId","FenceId" };
            string sql = SqlCommands.GetInsertSql("Orders", orderParams);
            sql = string.Format("{0}{1} WHERE OrderId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            #region 参数信息
            //运输订单信息
            parameters.Add("?TransportOrderId", orderEntity.TransportOrderId, DbType.Int64);
            parameters.Add("?TransportOrderNo", orderEntity.TransportOrderNo, DbType.AnsiString, size: 50);
            //基本信息
            parameters.Add("?OrderNo", orderEntity.OrderNo, DbType.AnsiString, size: 50);
            parameters.Add("?SourceType", orderEntity.SourceType.ToString(), DbType.AnsiString);
            parameters.Add("?IsVip", orderEntity.IsVip, DbType.Boolean);
            parameters.Add("?UserId", orderEntity.UserId, DbType.Int64, size: 20);
            parameters.Add("?Shipping", orderEntity.Shipping, DbType.AnsiString, size: 20);
            parameters.Add("?ShippingGender", orderEntity.ShippingGender, DbType.AnsiString, size: 20);
            parameters.Add("?ShippingPhone", orderEntity.ShippingPhone, DbType.AnsiString, size: 20);
            parameters.Add("?ShippingAddress", orderEntity.ShippingAddress, DbType.AnsiString, size: 200);
            parameters.Add("?Consignee", orderEntity.Consignee, DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneeGender", orderEntity.ConsigneeGender, DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneePhone", orderEntity.ConsigneePhone, DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneeAddress", orderEntity.ConsigneeAddress, DbType.AnsiString, size: 200);
            parameters.Add("?DriverRemark", orderEntity.DriverRemark, DbType.AnsiString, size: 400);
            parameters.Add("?ProvinceCode", orderEntity.ProvinceCode, DbType.AnsiString, size: 20);
            parameters.Add("?CityCode", orderEntity.CityCode, DbType.AnsiString, size: 20);
            parameters.Add("?CountyCode", orderEntity.CountyCode, DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", orderEntity.ProvinceName, DbType.AnsiString, size: 20);
            parameters.Add("?CityName", orderEntity.CityName, DbType.AnsiString, size: 20);
            parameters.Add("?CountyName", orderEntity.CountyName, DbType.AnsiString, size: 20);
            parameters.Add("?Longitude", orderEntity.Longitude, DbType.Double);
            parameters.Add("?Latitude", orderEntity.Latitude, DbType.Double);
            parameters.Add("?EndProvinceCode", orderEntity.EndProvinceCode, DbType.AnsiString, size: 20);
            parameters.Add("?EndCityCode", orderEntity.EndCityCode, DbType.AnsiString, size: 20);
            parameters.Add("?EndCountyCode", orderEntity.EndCountyCode, DbType.AnsiString, size: 20);
            parameters.Add("?EndProvinceName", orderEntity.EndProvinceName, DbType.AnsiString, size: 20);
            parameters.Add("?EndCityName", orderEntity.EndCityName, DbType.AnsiString, size: 20);
            parameters.Add("?EndCountyName", orderEntity.EndCountyName, DbType.AnsiString, size: 20);
            parameters.Add("?EndLongitude", orderEntity.EndLongitude, DbType.Double);
            parameters.Add("?EndLatitude", orderEntity.EndLatitude, DbType.Double);
            //货物信息
            parameters.Add("?GoodsPhoto", orderEntity.GoodsPhoto, DbType.AnsiString, size: 1000);
            parameters.Add("?GoodsName", orderEntity.GoodsName, DbType.AnsiString, size: 20);
            parameters.Add("?GoodsWeight", orderEntity.GoodsWeight, DbType.Decimal);
            parameters.Add("?GoodsVolume", orderEntity.GoodsVolume, DbType.Decimal);
            parameters.Add("?GoodsLength", orderEntity.GoodsLength, DbType.Decimal);
            parameters.Add("?GoodsWidth", orderEntity.GoodsWidth, DbType.Decimal);
            parameters.Add("?GoodsHeight", orderEntity.GoodsHeight, DbType.Decimal);
            parameters.Add("?TakeGoodsCode", orderEntity.TakeGoodsCode, DbType.AnsiString, size: 10);
            //保价相关信息
            parameters.Add("?InsuredConfigId", orderEntity.InsuredConfigId, DbType.Int32, size: 10);
            parameters.Add("?InsuredPriceCertificates", orderEntity.InsuredPriceCertificates, DbType.AnsiString, size: 1000);
            parameters.Add("?InsuredPrice", orderEntity.InsuredPrice, DbType.Decimal);
            parameters.Add("?InsuredPriceCharge", orderEntity.InsuredPriceCharge, DbType.Decimal);
            parameters.Add("?IsInsuredPriceHigh", orderEntity.IsInsuredPriceHigh, DbType.Boolean);
            parameters.Add("?InsuredPriceAmount", orderEntity.InsuredPriceAmount, DbType.Decimal);
            parameters.Add("?InsuredPriceChargeAmount", orderEntity.InsuredPriceChargeAmount, DbType.Decimal);
            //预约信息
            parameters.Add("?IsBook", orderEntity.IsBook, DbType.Boolean);
            parameters.Add("?BookTime", orderEntity.BookTime, DbType.DateTime);
            //支付方式
            parameters.Add("?PayMode", orderEntity.PayMode.ToString(), DbType.AnsiString);
            //运输业务相关信息
            parameters.Add("?VehicleTransportBusinessConfigId", orderEntity.VehicleTransportBusinessConfigId, DbType.Int32, size: 10);
            parameters.Add("?TransportBusiness", orderEntity.TransportBusiness, DbType.AnsiString);
            parameters.Add("?TransportBusinessStr", orderEntity.TransportBusinessStr, DbType.AnsiString);
            //营销优惠券相关信息
            parameters.Add("?MarketingCouponId", orderEntity.MarketingCouponId, DbType.Int64);
            parameters.Add("?CouponAmount", orderEntity.CouponAmount, DbType.Decimal);
            //车辆与业务相关信息
            parameters.Add("?VehicleBusinessMappingId", orderEntity.VehicleBusinessMappingId, DbType.Int32, size: 10);
            parameters.Add("?VehicleTypeId", orderEntity.VehicleTypeId, DbType.Int32, size: 10);
            parameters.Add("?VehicleTypeName", orderEntity.VehicleTypeName, DbType.AnsiString, size: 30);
            parameters.Add("?VehicleCategory", orderEntity.VehicleCategory, DbType.AnsiString, size: 150);
            parameters.Add("?VehicleCategoryName", orderEntity.VehicleCategory, DbType.AnsiString, size: 50);
            parameters.Add("?WithCarPeopleNumber", orderEntity.WithCarPeopleNumber, DbType.Int32, size: 10);
            parameters.Add("?WithCarPeoplePhone", orderEntity.WithCarPeoplePhone, DbType.AnsiString, size: 20);
            //运费相关配置
            parameters.Add("?FreightType", orderEntity.VehicleCategory, DbType.AnsiString, size: 20);
            parameters.Add("?FreightConfigurationId", orderEntity.FreightConfigurationId, DbType.Int32, size: 10);
            parameters.Add("?FreightConfigurationExtentionId", orderEntity.FreightConfigurationExtentionId, DbType.Int32, size: 10);
            parameters.Add("?HorsemanFreightConfigurationId", orderEntity.HorsemanFreightConfigurationId, DbType.Int64, size: 20);
            parameters.Add("?HorsemanFreightExtentionId", orderEntity.HorsemanFreightExtentionId, DbType.Int64, size: 20);
            parameters.Add("?StartPrice", orderEntity.StartPrice, DbType.Decimal);
            parameters.Add("?StartWeight", orderEntity.StartWeight, DbType.Int32, size: 10);
            parameters.Add("?StartMileage", orderEntity.StartMileage, DbType.Int32, size: 10);
            parameters.Add("?ContinueMileage", orderEntity.ContinueMileage, DbType.Int32, size: 10);
            parameters.Add("?ContinueWeight", orderEntity.ContinueWeight, DbType.Int32, size: 10);
            parameters.Add("?ContinueMileagePrice", orderEntity.ContinueMileagePrice, DbType.Decimal);
            parameters.Add("?ContinueWeightPrice", orderEntity.ContinueWeightPrice, DbType.Decimal);
            parameters.Add("?ContinueMileageAmount", orderEntity.ContinueMileageAmount, DbType.Decimal);
            parameters.Add("?ContinueWeightAmount", orderEntity.ContinueWeightAmount, DbType.Decimal);
            #endregion

            #region 套餐相关
            //夜间运费订单加收费用比例相关信息
            parameters.Add("?NightFreightOrderExtractProportion", orderEntity.NightFreightOrderExtractProportion, DbType.Decimal);
            parameters.Add("?NightFreightAdditionalAmount", orderEntity.NightFreightAdditionalAmount, DbType.Decimal);

            #region 搬家套餐相关
            //套餐业务相关信息
            parameters.Add("?VehicleSuiteBusinessConfigId", orderEntity.VehicleSuiteBusinessConfigId, DbType.Int32, size: 10);
            parameters.Add("?SuiteName", orderEntity.SuiteName, DbType.AnsiString, size: 20);
            parameters.Add("?SuiteBusinessType", orderEntity.SuiteBusinessType, DbType.AnsiString, size: 50);
            parameters.Add("?SuiteBusinessName", orderEntity.SuiteBusinessName, DbType.AnsiString, size: 50);
            //搬家规格与服务相关信息
            parameters.Add("?MoveHouseSpecificationService", orderEntity.MoveHouseSpecificationService, DbType.String);
            parameters.Add("?MoveHouseServiceAmount", orderEntity.MoveHouseServiceAmount, DbType.Decimal);
            parameters.Add("?SuiteCouponAmount", orderEntity.SuiteCouponAmount, DbType.Decimal);
            //套餐适合人群及套餐服务标题备注相关信息
            parameters.Add("?SuiteForCrowdTitle", orderEntity.SuiteForCrowdTitle, DbType.AnsiString, size: 30);
            parameters.Add("?SuiteForCrowdRemark", orderEntity.SuiteForCrowdRemark, DbType.AnsiString, size: 30);
            parameters.Add("?SuiteServiceTitle", orderEntity.SuiteServiceTitle, DbType.AnsiString, size: 30);
            parameters.Add("?SuiteServiceRemark", orderEntity.SuiteServiceRemark, DbType.AnsiString, size: 30);
            parameters.Add("?CarryServiceAmount", orderEntity.CarryServiceAmount, DbType.Decimal);
            #endregion
            //包装相关信息
            parameters.Add("?BigGoodsNumber", orderEntity.BigGoodsNumber, DbType.Int32);
            //包装相关信息
            parameters.Add("?PackingTitleStr", orderEntity.PackingTitleStr, DbType.String);
            parameters.Add("?PackingAmount", orderEntity.PackingAmount, DbType.Decimal);
            //特殊服务相关信息
            parameters.Add("?SpecificServiceTitleStr", orderEntity.SpecificServiceTitleStr, DbType.String);
            parameters.Add("?SpecificServiceAmount", orderEntity.SpecificServiceAmount, DbType.Decimal);
            #endregion

            #region 代收货款及打赏金额
            parameters.Add("?CodCost", orderEntity.CodCost, DbType.Decimal);
            parameters.Add("?CodCharge", orderEntity.CodCharge, DbType.Decimal);
            parameters.Add("?CodGoodsValueCertificates", orderEntity.CodGoodsValueCertificates, DbType.AnsiString, size: 1000);
            parameters.Add("?CodCostAmount", orderEntity.CodCostAmount, DbType.Decimal);
            parameters.Add("?CodChargeAmount", orderEntity.CodChargeAmount, DbType.Decimal);
            parameters.Add("?MusangAmount", orderEntity.MusangAmount, DbType.Decimal);
            #endregion

            #region 高德模式
            //高德模式
            parameters.Add("?MapTruckModeId", orderEntity.MapTruckModeId, DbType.Int32, size: 10);
            parameters.Add("?MapTruckTypeId", orderEntity.MapTruckTypeId, DbType.Int32, size: 10);
            #endregion

            //额外服务
            parameters.Add("?IsInsuredPrice", orderEntity.IsInsuredPrice, DbType.Boolean);
            parameters.Add("?IsCod", orderEntity.IsCod, DbType.Boolean);
            parameters.Add("?IsReceipt", orderEntity.IsReceipt, DbType.Boolean);
            parameters.Add("?IsInvoice", orderEntity.IsInvoice, DbType.Boolean);
            //账单
            parameters.Add("?OrderAmount", orderEntity.OrderAmount, DbType.Decimal);
            parameters.Add("?FreightCostAmount", orderEntity.FreightCostAmount, DbType.Decimal);
            //距离
            parameters.Add("?TotalDistanceLength", orderEntity.TotalDistanceLength, DbType.Decimal);
            parameters.Add("?IsInsuredContact", orderEntity.IsInsuredContact, DbType.Boolean);
            parameters.Add("?BookArrivalTime", orderEntity.BookArrivalTime, DbType.DateTime);
            parameters.Add("?OrderAmountSettleResult", orderEntity.OrderAmountSettleResult, DbType.AnsiString);
            parameters.Add("?AgentBranchesConfigId", orderEntity.AgentBranchesConfigId, DbType.Int64);
            //归属公司、代理商、围栏信息
            parameters.Add("CompanyId", orderEntity.CompanyId, DbType.Int64);
            parameters.Add("CompanyName", orderEntity.CompanyName, DbType.AnsiString);
            parameters.Add("AgentRecordId", orderEntity.AgentRecordId, DbType.Int64);
            parameters.Add("OneAgentRecordId", orderEntity.OneAgentRecordId, DbType.Int64);
            parameters.Add("TwoAgentRecordId", orderEntity.TwoAgentRecordId, DbType.Int64);
            parameters.Add("FenceId", orderEntity.FenceId, DbType.Int64);
            return await this.databaseContext.Master.QueryEntityAsync<OrderEntity>(sql, parameters);
        }
        #endregion

        public async Task<bool> CancelOrder(OrderEntity orderEntity)
        {
            string sql = @"UPDATE `Orders` SET 
                                OrderStatus='Cancel',
                                CloseOrderTime=now(),
                                CloseOrderContent=?CloseOrderContent
                            WHERE OrderId=?OrderId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderEntity.OrderId, DbType.Int64, size: 20);
            parameters.Add("?CloseOrderContent", orderEntity.CloseOrderContent, DbType.AnsiString, size: 400);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> CheckOrderNoExists(string orderNo)
        {
            string sql = string.Format("{0} WHERE OrderNo=?OrderNo LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderNo", orderNo, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> Delete(long orderId)
        {
            string sql = @"DELETE FROM `Orders` WHERE OrderId=?OrderId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<OrderEntity> GetEntity(string orderNo)
        {
            string sql = string.Format("{0} WHERE OrderNo=?OrderNo LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderNo", orderNo, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryEntityAsync<OrderEntity>(sql, parameters);
        }

        public async Task<OrderEntity> GetEntity(long orderId)
        {
            string sql = string.Format("{0} WHERE OrderId=?OrderId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await this.databaseContext.Slave.QueryEntityAsync<OrderEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<OrderEntity> GetEntityWithUser(long userId, long orderId)
        {
            string sql = string.Format("{0} WHERE OrderId=?OrderId AND UserId=?UserId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Slave.QueryEntityAsync<OrderEntity>(sql, parameters);
        }

        public async Task<OrderEntity> GetEntityWithDriver(long driverId, long orderId)
        {
            string sql = string.Format("{0} WHERE OrderId=?OrderId AND DriverId=?DriverId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            parameters.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Slave.QueryEntityAsync<OrderEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<OrderEntity>, long)> GetOrderEntities(int companyId, string vehicleNo, string provinceCode, string cityCode, string countyCode, OrderStatus? orderStatus,
            string goodsName, long? userId, long? driverId, string endProvinceCode, string endCommunityCode, string endCountyCode, decimal? goodsWeightLeft, decimal? goodsWeightRight,
            int? vehicleTypeId, TransportBusinessType? businessType, string vehicleCategoryName, string phone, DateTime? start, DateTime? end, bool? ascending,
            int pageIndex = 1, int pageSize = 10)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString, size: 20);
            }
            if (orderStatus.HasValue)
            {
                wheres.Add("OrderStatus=?OrderStatus");
                parameters.Add("?OrderStatus", orderStatus.Value.ToString(), DbType.AnsiString, size: 20);
            }

            if (!string.IsNullOrWhiteSpace(vehicleNo))
            {
                wheres.Add("VehicleNo LIKE CONCAT('%',CONCAT(?VehicleNo,'%'))");
                parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString, size: 10);
            }
            if (!string.IsNullOrWhiteSpace(goodsName))
            {
                wheres.Add("GoodsName LIKE CONCAT('%',CONCAT(?GoodsName,'%'))");
                parameters.Add("?GoodsName", goodsName, DbType.AnsiString, size: 100);
            }
            if (userId.HasValue)
            {
                wheres.Add("UserId=?UserId");
                parameters.Add("?UserId", userId, DbType.Int64, size: 20);
            }
            if (driverId.HasValue)
            {
                wheres.Add("DriverId=?DriverId");
                parameters.Add("?DriverId", driverId, DbType.Int64, size: 20);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime< ?End");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            //因为前端传的是吨位，所以需要乘1000
            if (goodsWeightLeft.HasValue)
            {
                wheres.Add("GoodsWeight >= ?GoodsWeightLeft");
                parameters.Add("?GoodsWeightLeft", goodsWeightLeft.Value * 1000, DbType.Decimal);
            }
            if (goodsWeightRight.HasValue)
            {
                wheres.Add("GoodsWeight <= ?GoodsWeightRight");
                parameters.Add("?GoodsWeightRight", goodsWeightRight.Value * 1000, DbType.Decimal);
            }
            if (vehicleTypeId.HasValue)
            {
                wheres.Add("VehicleTypeId = ?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int32);
            }
            if (businessType.HasValue)
            {
                wheres.Add("BusinessType = ?BusinessType");
                parameters.Add("?BusinessType", businessType.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleCategoryName))
            {
                wheres.Add("VehicleCategoryName LIKE CONCAT('%',CONCAT(?VehicleCategoryName,'%'))");
                parameters.Add("?VehicleCategoryName", vehicleCategoryName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("ShippingPhone = ?ShippingPhone");
                parameters.Add("?ShippingPhone", phone, DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime DESC LIMIT {2},{3};
                                        SELECT Count(1) FROM `Orders` {1}",
                QUERY,
              wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
              (pageIndex - 1) * pageSize,
              pageSize);

            return await this.databaseContext.Slave.QueryPageableListAsync<OrderEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<OrderEntity>, int)> GetOrderEntities(UserAppOrderStatus? userAppOrderStatus, long? userId, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (userAppOrderStatus.HasValue && userAppOrderStatus.Value != UserAppOrderStatus.All)
            {
                List<string> statusList = new List<string>();
                switch (userAppOrderStatus.Value)
                {
                    case UserAppOrderStatus.Dispatching:
                        statusList.Add(OrderStatus.Dispatching.ToString());
                        statusList.Add(OrderStatus.Pickup.ToString());
                        statusList.Add(OrderStatus.PlaceOrder.ToString());
                        break;
                    case UserAppOrderStatus.Transporting:
                        statusList.Add(OrderStatus.Transporting.ToString());
                        break;
                    case UserAppOrderStatus.Acceptance:
                        statusList.Add(OrderStatus.Acceptance.ToString());
                        break;
                    case UserAppOrderStatus.Complete:
                        statusList.Add(OrderStatus.Complete.ToString());
                        break;
                }

                wheres.Add("FIND_IN_SET(OrderStatus,?OrderStatus)");
                parameters.Add("?OrderStatus", string.Join(",", statusList.ToArray()), DbType.AnsiString);
            }
            if (userId.HasValue)
            {
                wheres.Add("UserId=?UserId");
                parameters.Add("?UserId", userId, DbType.Int64, size: 20);
            }

            wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
            parameters.Add("?Start", DateTime.Now.AddMonths(-6), DbType.DateTime);
            parameters.Add("?End", DateTime.Now, DbType.DateTime);


            string sql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM `Orders` {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex - 1) * pageSize,
                  pageSize,
                  "DESC");

            return await this.databaseContext.Slave.QueryPageableListAsync<OrderEntity>(sql, parameters);
        }


        #region 司机端独立使用
        public async Task<(IEnumerable<OrderEntity>, int)> GetOrderEntities(DriverAppOrderStatus? driverAppOrderStatus, long? driverId, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (driverId.HasValue)
            {
                wheres.Add("DriverId=?DriverId");
                parameters.Add("?DriverId", driverId, DbType.Int64, size: 20);
            }

            wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
            parameters.Add("?Start", DateTime.Now.AddMonths(-6), DbType.DateTime);
            parameters.Add("?End", DateTime.Now, DbType.DateTime);

            if (driverAppOrderStatus.HasValue && driverAppOrderStatus.Value != DriverAppOrderStatus.All)
            {
                List<string> statusList = new List<string>();
                switch (driverAppOrderStatus.Value)
                {
                    case DriverAppOrderStatus.Pickup:
                        statusList.Add(OrderStatus.Dispatching.ToString());
                        statusList.Add(OrderStatus.Pickup.ToString());
                        statusList.Add(OrderStatus.PlaceOrder.ToString());
                        break;
                    case DriverAppOrderStatus.Transporting:
                        statusList.Add(OrderStatus.Transporting.ToString());
                        break;
                    case DriverAppOrderStatus.Acceptance:
                        statusList.Add(OrderStatus.Acceptance.ToString());
                        break;
                    case DriverAppOrderStatus.Complete:
                        statusList.Add(OrderStatus.Complete.ToString());
                        break;
                }
                wheres.Add("FIND_IN_SET(OrderStatus,?OrderStatus)");
                parameters.Add("?OrderStatus", string.Join(",", statusList), DbType.AnsiString);
            }

            string sql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM `Orders` {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex - 1) * pageSize,
                  pageSize,
                  "DESC");

            return await this.databaseContext.Slave.QueryPageableListAsync<OrderEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<OrderEntity>, int)> GetOrderEntities(DriverEntity currentDriver, OrderStatus orderStatus, TransportBusinessType? transportBusinessType, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("FIND_IN_SET(OrderStatus,?OrderStatus)");
            parameters.Add("?OrderStatus", orderStatus.ToString(), DbType.AnsiString);
            //未接单的订单
            wheres.Add("(DriverId is null Or DriverId=0)");

            if (currentDriver != null)
            {
                wheres.Add("VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", currentDriver.VehicleTypeId, DbType.Int32);

                wheres.Add("CountyCode=?CountyCode");
                parameters.Add("?CountyCode", currentDriver.CountyCode, DbType.AnsiString);
            }
            if (transportBusinessType.HasValue)
            {
                wheres.Add("TransportBusinessType=?TransportBusinessType");
                parameters.Add("?TransportBusinessType", transportBusinessType.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM `Orders` {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex - 1) * pageSize,
                  pageSize,
                  "DESC");
            return await this.databaseContext.Slave.QueryPageableListAsync<OrderEntity>(sql, parameters);
        }

        #region 设置原始回单
        public async Task<bool> AddSourceReceiptPhoto(OrderEntity orderEntity)
        {
            string sql = SqlCommands.GetUpdateSql("`Orders`", new string[] { "ReceiptPhoto" }, new string[] { "OrderId" });
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?OrderId", orderEntity.OrderId, DbType.Int64, size: 20);
            parameter.Add("?ReceiptPhoto", orderEntity.ReceiptPhoto, DbType.AnsiString, size: 1000);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameter, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 车辆上传签收回单
        public async Task<bool> AddAcceptanceReceiptPhoto(OrderEntity orderEntity)
        {
            string sql = SqlCommands.GetUpdateSql("`Orders`", new string[] { "ReceiptAcceptancePhoto" }, new string[] { "OrderId" });
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?OrderId", orderEntity.OrderId, DbType.Int64, size: 20);
            parameter.Add("?ReceiptAcceptancePhoto", orderEntity.ReceiptAcceptancePhoto, DbType.AnsiString, size: 1000);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameter,databaseContext.Transaction)>0;
        }
        #endregion
        #endregion
        public Task<(IEnumerable<OrderEntity>, int)> GetOrderEntities(AgentAppOrderStatus? agentAppOrderStatus, long agentRecordId, int pageIndex, int pageSize)
        {
            throw new NotImplementedException();
        }

        public Task<IEnumerable<long>> GetOrderIds(long vehicleId)
        {
            throw new NotImplementedException();
        }

        public Task<(IEnumerable<OrderEntity>, int)> GetRefundOrders(string provinceId, string communityId, string countyId, bool? hasRefund, int pageIndex, int pageSize)
        {
            throw new NotImplementedException();
        }

        public async Task<bool> RollbackOrderStatus(long orderId, string orderNo, OrderStatus orderStatus)
        {
            var updateOneSql = SqlCommands.GetUpdateSql("`Orders`", new string[] { "OrderStatus", "CompleteTime" }, new string[] { "OrderId" });
            var updateTwoSql = SqlCommands.GetUpdateSql("OrderExtention", new string[] { "OrderStatus" }, new string[] { "OrderNo" });
            string sql = string.Format("{0}{1}", updateOneSql, updateTwoSql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.Complete.ToString(), DbType.AnsiString);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            parameters.Add("?CompleteTime", orderStatus.ToString(), DbType.AnsiString);
            parameters.Add("?OrderStatus", OrderStatus.Complete.ToString(), DbType.AnsiString);
            parameters.Add("?OrderNo", orderNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderAmount(long orderId, decimal freightCostAmount, decimal orderAmount)
        {
            string sql = SqlCommands.GetUpdateSql("`Orders`", new string[] { "FreightCostAmount", "OrderAmount" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightCostAmount", freightCostAmount, DbType.Decimal);
            parameters.Add("?OrderAmount", orderAmount, DbType.Decimal);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderConsigneePayTime(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new string[] { "ConsigneePayTime" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ConsigneePayTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderShippingPayTime(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new string[] { "ShippingPayTime" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ShippingPayTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderStatusAcceptance(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("`Orders`", new string[] { "OrderStatus" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.Acceptance.ToString(), DbType.AnsiString, size: 20);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters,databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderStatusArrived(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new string[] { "OrderStatus" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.Arrived.ToString(), DbType.AnsiString);
            parameters.Add("?OrderId", orderId, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderStatusClosed(long editerId, string editer, long orderId, string orderNo, string closeOrderContent)
        {
            //string insertSql = @"INSERT INTO OrderAggregateCloseRecord(OrderAggregateId,OrderAggregateNo,OrderStatus,CloseContent,CloseEditerId,CloseEditer,AssignStatus,OrderType) 
            //            SELECT A.OrderId,A.OrderNo,A.OrderStatus,?CloseContent,?CloseEditerId,?CloseEditer,B.AssignStatus,?OrderType FROM `Order` A  
            //            LEFT JOIN OrderPool B ON B.OrderId = A.OrderId 
            //            WHERE A.OrderId=?OrderId;";

            string sql = SqlCommands.GetUpdateSql("`Orders`", new string[] { "OrderStatus", "CloseOrderTime", "CloseOrderContent", "CloseEditerId", "CloseEditer" }, new string[] { "OrderId" });
            sql = string.Format("{0} UPDATE OrderExtention SET OrderStatus=?OrderStatus WHERE OrderNo=?OrderNo;", sql);
            sql = string.Format("{0} UPDATE OrderPool SET AssignStatus='Done' WHERE OrderId=?OrderId;", sql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.Closed.ToString(), DbType.AnsiString);
            parameters.Add("?CloseOrderTime", DateTime.Now);
            parameters.Add("?CloseOrderContent", closeOrderContent, DbType.AnsiString, size: 400);
            parameters.Add("?CloseEditer", editer, DbType.AnsiString, size: 50);
            parameters.Add("?CloseEditerId", editerId, DbType.Int64, size: 20);
            parameters.Add("?OrderNo", orderNo, DbType.AnsiString, size: 50);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            parameters.Add("?OrderType", "Small", DbType.AnsiString);
            parameters.Add("?CloseContent", closeOrderContent, DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderStatusComplete(long orderId, string orderNo)
        {
            var updateOneSql = SqlCommands.GetUpdateSql("`Orders", new string[] { "OrderStatus", "CompleteTime" }, new string[] { "OrderId" });
            var updateTwoSql = SqlCommands.GetUpdateSql("OrderExtention", new string[] { "OrderStatus" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.Complete.ToString(), DbType.AnsiString);
            parameters.Add("?CompleteTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            string sql = string.Format("{0} {1}", updateOneSql, updateTwoSql);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderStatusPlaceOrder(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("`Orders", new string[] { "OrderStatus", "DriverId", "VehicleNo", "DriverName", "DriverPhone" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.PlaceOrder.ToString(), DbType.AnsiString);
            parameters.Add("?OrderId", orderId, DbType.Int64, size: 20);
            parameters.Add("?DriverId", null, DbType.Int64, size: 20);
            parameters.Add("?VehicleNo", null, DbType.AnsiString, size: 20);
            parameters.Add("?DriverName", null, DbType.AnsiString, size: 20);
            parameters.Add("?DriverPhone", null, DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateOrderStatusTransporting(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new string[] { "OrderStatus" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderStatus", OrderStatus.Transporting.ToString(), DbType.AnsiString);
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction)>0;
        }
        public async Task<bool> UpdateOrderAssigned(long orderId, long driverId, string driverName, string driverPhone, string vehicleNo, OrderStatus orderStatus, long assignEditerId, string assignEditer)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new[] { "DriverId", "DriverName", "DriverPhone", "VehicleNo", "OrderStatus", "AssignEditerId", "AssignEditer" }, new[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", driverId, DbType.Int64);
            parameters.Add("?DriverName", driverName, DbType.AnsiString);
            parameters.Add("?DriverPhone", driverPhone, DbType.AnsiString);
            parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
            parameters.Add("?OrderStatus", orderStatus.ToString(), DbType.AnsiString);
            parameters.Add("?AssignEditerId", assignEditerId, DbType.Int64);
            parameters.Add("?AssignEditer", assignEditer, DbType.AnsiString);
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, this.databaseContext.Transaction)>0;
        }

        public async Task<bool> UpdateOrderDriver(long orderId, long driverId, string driverName, string driverPhone, string vehicleNo)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new[] { "DriverId", "DriverName", "DriverPhone", "VehicleNo" }, new[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", driverId, DbType.Int64);
            parameters.Add("?DriverName", driverName, DbType.AnsiString);
            parameters.Add("?DriverPhone", driverPhone, DbType.AnsiString);
            parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, this.databaseContext.Transaction) > 0;
        }
        public async Task<bool> UpdateOrderAdmountSettleResult(long orderId, OrderAmountSettleResultType orderAmountSettleResultType)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new[] { "OrderAmountSettleResult" }, new[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            parameters.Add("?OrderAmountSettleResult", orderAmountSettleResultType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, this.databaseContext.Transaction) > 0;

        }

        public async Task<long> GetListByAgentRecordId(long agentRecordId, OrderStatus orderStatus, DateTime? completeTime)
        {
            string sql = "SELECT COUNT(1) FROM Orders";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            wheres.Add("OrderStatus = ?OrderStatus");
            parameters.Add("?OrderStatus", orderStatus.ToString(), DbType.AnsiString);
            if (completeTime.HasValue)
            {
                wheres.Add("DATE(CompleteTime) = ?CompleteTime");
                parameters.Add("?CompleteTime", completeTime.Value.Date, DbType.Date);
            }
            sql = string.Format("{0} {1}", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await databaseContext.Slave.QueryEntityAsync<long>(sql, parameters);
        }

        public async Task<IEnumerable<OrderStatisticInfoEntity>> GetOrderStatisticInfo(long agentRecordId, OrderStatus orderStatus, DateTime? completeTime)
        {
            string sql = @"SELECT TransportBusiness,TransportBusinessStr,COUNT(OrderId) AS OrderTotalNumber,SUM(IFNULL(OrderAmount,0)) AS OrderTotalAmount 
                            FROM Orders";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            wheres.Add("OrderStatus = ?OrderStatus");
            parameters.Add("?OrderStatus", orderStatus.ToString(), DbType.AnsiString);
            if (completeTime.HasValue)
            {
                wheres.Add("DATE(CompleteTime) = ?CompleteTime");
                parameters.Add("?CompleteTime", completeTime.Value.Date, DbType.Date);
            }
            sql = string.Format("{0} {1} GROUP BY TransportBusiness", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await databaseContext.Slave.QueryAsync<OrderStatisticInfoEntity>(sql, parameters);
        }

        public async Task<IEnumerable<OrderAreaAnalysisDetailEntity>> GetOrderAreaAnalysisDetailList(long agentRecordId, OrderStatus orderStatus, DateTime? completeTime)
        {
            string sql = @"SELECT TransportBusiness,TransportBusinessStr,CountyCode,CountyName,COUNT(OrderId) AS OrderTotalNumber
                            FROM Orders";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            wheres.Add("OrderStatus = ?OrderStatus");
            parameters.Add("?OrderStatus", orderStatus.ToString(), DbType.AnsiString);
            if (completeTime.HasValue)
            {
                wheres.Add("DATE(CompleteTime) = ?CompleteTime");
                parameters.Add("?CompleteTime", completeTime.Value.Date, DbType.Date);
            }
            sql = string.Format("{0} {1} GROUP BY TransportBusiness,CountyCode", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await databaseContext.Slave.QueryAsync<OrderAreaAnalysisDetailEntity>(sql, parameters);
        }

        public async Task<bool> UpdateOrderSignPersonInfo(OrderEntity orderEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Orders", new string[] { "OrderSignTime", "OrderSignPerson", "OrderSignPersonPhone", "OrderSignPhoto" }, new string[] { "OrderId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderSignTime", orderEntity.OrderSignTime, DbType.DateTime);
            parameters.Add("?OrderSignPerson", orderEntity.OrderSignPerson, DbType.AnsiString);
            parameters.Add("?OrderSignPersonPhone", orderEntity.OrderSignPersonPhone, DbType.AnsiString);
            parameters.Add("?OrderSignPhoto", orderEntity.OrderSignPhoto, DbType.AnsiString);
            parameters.Add("?OrderId", orderEntity.OrderId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction)>0;
        }
    }
}



